package com.zsyc.model;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import java.util.logging.Level;
import com.zsyc.util.DatabaseManager;

/**
 * 单词模型类
 */
public class Word {
    private static final Logger LOGGER = Logger.getLogger(Word.class.getName());
    
    private Integer wordId;
    private String englishWord;
    private String chineseMeaning;
    private String createdAt;
    
    public Word() {}
    
    public Word(String englishWord, String chineseMeaning) {
        this.englishWord = englishWord;
        this.chineseMeaning = chineseMeaning;
    }
    
    // Getters and Setters
    public Integer getWordId() { return wordId; }
    public void setWordId(Integer wordId) { this.wordId = wordId; }
    
    public String getEnglishWord() { return englishWord; }
    public void setEnglishWord(String englishWord) { this.englishWord = englishWord; }
    
    public String getChineseMeaning() { return chineseMeaning; }
    public void setChineseMeaning(String chineseMeaning) { this.chineseMeaning = chineseMeaning; }
    
    public String getCreatedAt() { return createdAt; }
    public void setCreatedAt(String createdAt) { this.createdAt = createdAt; }
    
    /**
     * 保存单词到数据库
     */
    public boolean save() {
        String sql = "INSERT INTO word (english_word, chinese_meaning) VALUES (?, ?)";
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, englishWord);
            pstmt.setString(2, chineseMeaning);
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                // 获取最后插入的ID
                try (Statement stmt = conn.createStatement();
                     ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()")) {
                    if (rs.next()) {
                        this.wordId = rs.getInt(1);
                    }
                }
                LOGGER.info("单词保存成功: " + englishWord + " (ID: " + wordId + ")");
                return true;
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "保存单词失败: " + englishWord, e);
        }
        return false;
    }
    
    /**
     * 从数据库获取所有单词
     */
    public static List<Word> getAllWords() {
        List<Word> words = new ArrayList<>();
        String sql = "SELECT * FROM word ORDER BY created_at DESC";
        
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            
            while (rs.next()) {
                Word word = new Word();
                word.setWordId(rs.getInt("word_id"));
                word.setEnglishWord(rs.getString("english_word"));
                word.setChineseMeaning(rs.getString("chinese_meaning"));
                word.setCreatedAt(rs.getString("created_at"));
                words.add(word);
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "获取单词列表失败", e);
        }
        return words;
    }
    
    /**
     * 从数据库获取指定数量的随机单词
     */
    public static List<Word> getRandomWords(int count) {
        List<Word> words = new ArrayList<>();
        String sql = "SELECT * FROM word ORDER BY RANDOM() LIMIT ?";
        
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, count);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Word word = new Word();
                    word.setWordId(rs.getInt("word_id"));
                    word.setEnglishWord(rs.getString("english_word"));
                    word.setChineseMeaning(rs.getString("chinese_meaning"));
                    word.setCreatedAt(rs.getString("created_at"));
                    words.add(word);
                }
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "获取随机单词失败", e);
        }
        return words;
    }
    
    /**
     * 搜索单词
     */
    public static List<Word> searchWords(String keyword) {
        List<Word> words = new ArrayList<>();
        String sql = "SELECT * FROM word WHERE english_word LIKE ? OR chinese_meaning LIKE ? ORDER BY created_at DESC";
        
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            String searchPattern = "%" + keyword + "%";
            pstmt.setString(1, searchPattern);
            pstmt.setString(2, searchPattern);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Word word = new Word();
                    word.setWordId(rs.getInt("word_id"));
                    word.setEnglishWord(rs.getString("english_word"));
                    word.setChineseMeaning(rs.getString("chinese_meaning"));
                    word.setCreatedAt(rs.getString("created_at"));
                    words.add(word);
                }
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "搜索单词失败", e);
        }
        return words;
    }
    
    /**
     * 批量导入单词
     */
    public static boolean importWords(List<Word> words) {
        String sql = "INSERT OR IGNORE INTO word (english_word, chinese_meaning) VALUES (?, ?)";
        
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            conn.setAutoCommit(false);
            for (Word word : words) {
                pstmt.setString(1, word.getEnglishWord());
                pstmt.setString(2, word.getChineseMeaning());
                pstmt.addBatch();
            }
            
            int[] results = pstmt.executeBatch();
            conn.commit();
            conn.setAutoCommit(true);
            
            int successCount = 0;
            for (int result : results) {
                if (result >= 0) successCount++;
            }
            
            LOGGER.info("成功导入 " + successCount + " 个单词");
            return successCount > 0;
            
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "批量导入单词失败", e);
            return false;
        }
    }
    
    /**
     * 删除单词
     */
    public boolean delete() {
        String sql = "DELETE FROM word WHERE word_id = ?";
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, wordId);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "删除单词失败", e);
            return false;
        }
    }
    
    /**
     * 清空所有单词
     */
    public static boolean clearAllWords() {
        String sql = "DELETE FROM word";
        try (Connection conn = DatabaseManager.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            int affectedRows = pstmt.executeUpdate();
            LOGGER.info("清空单词库，删除了 " + affectedRows + " 个单词");
            return true;
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "清空单词库失败", e);
            return false;
        }
    }
}
